package com.zret.oasys.dao.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.zret.oasys.dao.AttendanceDao;
import com.zret.oasys.dbtools.JDBCUtils;
import com.zret.oasys.dbtools.PageData;
import com.zret.oasys.entity.Attence;
import com.zret.oasys.entity.AttenceTable;
import com.zret.oasys.entity.Emp;

public class AttendanceDaoImpl implements AttendanceDao {

	@Override
	public boolean addAttendance(Attence attence) {
		String sql="insert into attence values (?,?,?,?)";
		long serialnum = System.nanoTime();
		int n = JDBCUtils.exUpdate(sql, serialnum,attence.getEmpid(),attence.getAttencetime(),attence.getAttencetype());
		return n>0;
	}
	

	@Override
	public boolean delAttendance(int serialnum) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public PageData<Attence> getAttendanceByEmpidPage(int empid) {
		// TODO Auto-generated method stub
		return null;
	}
	
	
	
	
	
    
	/**
	 * 根据考勤的状态查询(分页)
	 */
	@Override
	public PageData<Attence> getAttendanceByTypePage(int page, int pageSize, String attencetype) {
		//调用工具类
		String sql="select serialnum,empid,attencetime,attencetype from attence where attencetype=?";
		PageData<Attence> attendancebytypePage = JDBCUtils.exQueryByPage(sql, Attence.class, page, pageSize, attencetype);

		return attendancebytypePage;
	}

	

	
	/**
	 * 根据考勤状态查询实现类
	 */
	@Override
	public List<Attence> getAttendanceByType(String attencetype) {
		String sql="select serialnum,empid,attencetime,attencetype from attence where attencetype=?";
		List<Attence> attencelist = JDBCUtils.exQuery(sql, Attence.class, attencetype);
		return attencelist;
		
	}
	

	
	/**
	 * 根据员工id,考勤类型和考勤时间模糊查询（如：2020-05-08来查询，我们想查是当前日期）
	 * @param empid 员工编号
	 * @param attencetime 当前日期  如2020-05-08 不包含时间
	 * @param attencetype 考勤类型状态
	 * @return List<Attence>
	 */
	@Override
	public List<Attence> getAttendanceByIdTypeTime(int empid, String attencetime, String attencetype) {
		String sql="select serialnum,empid,attencetime,attencetype from attence where empid=? AND attencetime LIKE ? AND attencetype=?";
		List<Attence> list = JDBCUtils.exQuery(sql, Attence.class, empid,attencetime+"%",attencetype);
		return list;
	}
	
	

	
	/**
	 * 根据员工id,日期考勤开始和结束时间和考勤类型来查询 (如可根据当前日期，查询不同时间段) [ ] 双闭合时间和半闭合( ] 
	 * 后续可扩展
	 * @param select 选择要执行的sql 比如1,2,3
	 * @param empid 员工id
	 * @param dateString 日期
	 * @param starttime 开始时间
	 * @param endtime  结束时间
	 * @param attencetype 考勤类型
	 * @return List<Attence>
	 */
	@Override
	public List<Attence> getAttendByIdStarEndTimeType(String select, int empid, String dateString, String starttime,
			String endtime, String attencetype) {
		List<Attence> list=null;
		String sql=null;
		//1是全闭合时间查询 [ ]
		if ("1".equals(select)) {
			sql="SELECT serialnum,empid,attencetime,attencetype FROM attence WHERE attencetime BETWEEN ? AND ? AND empid=? AND attencetype=?";
			list = JDBCUtils.exQuery(sql, Attence.class, dateString+" "+starttime,dateString+" "+endtime,empid,attencetype);
		}//2是半闭合查询，( ]
		else if ("2".equals(select)) {
			sql="SELECT * FROM attence WHERE attencetime > ? AND attencetime <= ? AND empid=? AND attencetype=?";
			list = JDBCUtils.exQuery(sql, Attence.class, dateString+" "+starttime,dateString+" "+endtime,empid,attencetype);
		}

		return list;
	}
	
	
	/**
	 * 联合查询，考勤表和员工表联合查询，查询员工信息（我主要目的是要获取员工id）
	 * @return List<Emp>
	 */
	@Override
	public List<Emp> getAttendAndEmpIsEmpid() {
		String sql="SELECT e.empid,e.position,e.deptid,e.empname,e.sex,e.hiredate,e.salary,e.username,e.`password`,e.faceflag \r\n" + 
				"FROM emp AS e LEFT JOIN attence AS a ON a.empid=e.empid WHERE ISNULL(a.serialnum);";
		List<Emp> list = JDBCUtils.exQuery(sql, Emp.class);
		return list;
	}
	
	
	/**
	 * 根据考勤日期获取考勤记录（分页）
	 * @param page 当前页
	 * @param pageSize  每页记录数
	 * @param attencetime 考勤日期
	 * @return   PageData<AttenceTable>   泛型：AttenceTable实体类是多表联合查询创建的实体类
	 */
	@Override
	public PageData<AttenceTable> getAttendanceByDatePage(int page, int pageSize, String attencetime) {
		String sql="SELECT atc.serialnum,dt.deptid,dt.deptname,atc.empid,em.empname,em.position,atc.attencetime,atc.attencetype FROM attence AS atc INNER JOIN emp AS em INNER JOIN dept AS dt\r\n" + 
				"ON atc.empid=em.empid and em.deptid=dt.deptid WHERE atc.attencetime LIKE ?";
		PageData<AttenceTable> atablePD = JDBCUtils.exQueryByPage(sql, AttenceTable.class, page, pageSize, attencetime+"%");
		
		
		return atablePD;
	}
	
	
	
	
	/**
	 * 根据流水号修改员工考勤状态
	 * @param serialnum 流水号
	 * @param attencetype 考勤状态（到岗，迟到，请假，旷工）
	 * @return ture修改成功
	 */
	@Override
	public boolean updateAttendanceType(long serialnum, String attencetype) {
		String sql="UPDATE attence SET attencetype=? WHERE serialnum=?";
		int n = JDBCUtils.exUpdate(sql, attencetype,serialnum);
		
		return n>0;
		
	}
	
	
	
	/**
	 * 根据员工id，考勤类型和考勤日期的模糊查询
	 * @param empid 员工id
	 * @param attenceDate 日期
	 * @param attencetype 考勤类型
	 * @return   List<Attence>
	 */
	@Override
	public List<Attence> getAttendanceByIdLikeTypeDate(int empid, String attenceDate, String attencetype) {
		// TODO Auto-generated method stub
		String sql="SELECT serialnum,empid,attencetime,attencetype FROM attence WHERE attencetime LIKE ? AND attencetype LIKE ? AND empid=?";
		List<Attence> list = JDBCUtils.exQuery(sql, Attence.class, attenceDate+"%","%"+attencetype+"%",empid);
		return list;
		
	}
	
	

	/**
	 * 根据员工编号模糊查询日期（分页）
	 * @param empid  员工编号
	 * @param attenceDate 考勤日期（只有日期）
	 * @return PageData<Attence>
	 */
	@Override
	public PageData<AttenceTable> getAttendanceByIdLikeDate(int page, int pageSize, int empid, String attenceDate) {
		String sql="SELECT atc.serialnum,dt.deptid,dt.deptname,atc.empid,em.empname,em.position,atc.attencetime,atc.attencetype FROM attence AS atc INNER JOIN emp AS em INNER JOIN dept AS dt\r\n" + 
				"ON atc.empid=em.empid and em.deptid=dt.deptid WHERE atc.attencetime LIKE ? AND em.empid=?";
		PageData<AttenceTable> pd = JDBCUtils.exQueryByPage(sql, AttenceTable.class, page, pageSize, attenceDate+"%",empid);
		
		return pd;
	}
	
	
	
	
}
